package jp.ac.u_tokyo.dch.dao.front;

import java.util.ArrayList;
import java.util.List;

import jp.ac.u_tokyo.dch.core.db.CustomPreparedStatement;
import jp.ac.u_tokyo.dch.core.db.CustomResultSet;
import jp.ac.u_tokyo.dch.core.db.DBConnection;
import jp.ac.u_tokyo.dch.core.exceptions.DBException;
import jp.ac.u_tokyo.dch.model.db.FrontDisplayGroup;
import jp.ac.u_tokyo.dch.model.db.GroupItems;
import jp.ac.u_tokyo.dch.model.xml.tree.TreeData;
import jp.ac.u_tokyo.dch.util.common.CommonDBConnection;

import org.apache.commons.lang.xwork.StringUtils;
/**
* @version 1.1
*/
public class FrontDisplayGroupDao {
	/**
	 * DBコネクションオブジェクト
	 */
	private DBConnection dbconn = null;

	private String SELECT_ITEM = "L%d.datacontent";

	private String SELECT_FIELDKEY = "(SELECT * FROM total_data_items WHERE FIELDKEY = '%s') AS L%d ";

	private String SELECT_JOIN_CONDITION = "on  L%d.TOTAL_DATA_ID = L%d.TOTAL_DATA_ID ";

	private String ORDERBY_ITEM = " L%d.datacontent desc ";

	/**
	 * 生成者
	 * @param DBコネクションオブジェクト
	 */
	public FrontDisplayGroupDao(DBConnection dbconn){
		this.dbconn = dbconn;
	}

	public List<List<TreeData>> selectGroupData(List<String> fieldList) throws DBException {

		List<List<TreeData>> list = new ArrayList<List<TreeData>>();

		String sqlQuery = makeGroupResultQuery(fieldList);

		int size = fieldList.size();

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			ps = dbconn.prepareStatement(sqlQuery);
			rs = ps.executeQuery();

			while (rs.next()) {

				List<TreeData> dataList = new ArrayList<TreeData>();

				String label = null;
				String id = null;
				TreeData treeData = null;
				for (int i = 0; i < size; i++) {

					label = rs.getString(String.format(SELECT_ITEM, i+1));

					if (i == 0) {
						id = rs.getString("L1.TOTAL_DATA_ID");
					}

					if (label == null) {
						treeData = new TreeData("その他", id);
					} else {
						treeData = new TreeData(label, id);
					}

					dataList.add(treeData);
				}

				dataList.add(new TreeData(getDataName(id), id));

				list.add(dataList);
			}

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}

		return list;

	}

	/**
	 * 開始日付が最新のグループ情報を取得する
	 * @return グループリスト
	 * @throws DBException 例外
	 */
	public List<GroupItems> selectMaxList() throws DBException {

		List<GroupItems> list = new ArrayList<GroupItems>();

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("SELECT group_items.displayname,fieldkey FROM  ");
		sqlQuery.append("(front_display_group  ");
		sqlQuery.append("INNER JOIN (SELECT id, max(concat(startDate, starttime)) FROM front_display_group) AS maxGroup ");
		sqlQuery.append("on front_display_group.id = maxGroup.id) INNER JOIN group_items on front_display_group.id = group_items.groupid ");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			ps = dbconn.prepareStatement(sqlQuery.toString());
			rs = ps.executeQuery();

			while (rs.next()) {

				GroupItems groupItems = new GroupItems();

				groupItems.setDisplayName(rs.getString("displayname"));
				//groupItems.setFieldKeyList(selectFieldList(rs.getString("fieldkey")));

				list.add(groupItems);
			}

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}

		return list;
	}


	/**
	 * フロントディレクトリ表示設定リストを返す
	 * @return フロントディレクトリ表示
	 * @throws DBException 例外
	 */
	public List<FrontDisplayGroup> selectList() throws DBException {

		List<FrontDisplayGroup> list = new ArrayList<FrontDisplayGroup>();

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("SELECT * ");
		sqlQuery.append("FROM ");
		sqlQuery.append("front_display_group ");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			ps = dbconn.prepareStatement(sqlQuery.toString());
			rs = ps.executeQuery();

			while (rs.next()) {

				FrontDisplayGroup frontDisplayGroup = new FrontDisplayGroup();

				frontDisplayGroup.setId(rs.getString("id"));
				frontDisplayGroup.setDisplayName(rs.getString("displayname"));
				frontDisplayGroup.setStartDate(rs.getString("startdate"));
				frontDisplayGroup.setStartTime(rs.getString("starttime"));

				list.add(frontDisplayGroup);
			}

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return list;
	}

	/**
	 * フロントディレクトリ表示設定オブジェクトを返す
	 * @param groupid クループID
	 * @return ロントディレクトリ表示設定オブジェクト
	 * @throws DBException 例外
	 */
	public FrontDisplayGroup selectObject(String groupid) throws DBException {

		FrontDisplayGroup frontDisplayGroup = new FrontDisplayGroup();

		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("SELECT * ");
		sqlQuery.append("FROM ");
		sqlQuery.append("front_display_group where id = ?");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setInt(1, Integer.valueOf(groupid));
			rs = ps.executeQuery();

			if (rs.next()) {

				frontDisplayGroup = new FrontDisplayGroup();

				frontDisplayGroup.setId(rs.getString("id"));
				frontDisplayGroup.setDisplayName(rs.getString("displayname"));
				frontDisplayGroup.setStartDate(rs.getString("startdate"));
				frontDisplayGroup.setStartTime(rs.getString("starttime"));

			}

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}
		return frontDisplayGroup;
	}

	/**
	 * フロントディレクトリ表示設定を挿入する
	 * @param frontDisplayGroup フロントディレクトリ表示設定
	 * @return 挿入件数
	 * @throws DBException 例外
	 */
	public int insert(FrontDisplayGroup frontDisplayGroup) throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		int index = 1;
		try{
			String query = "INSERT INTO front_display_group (id, displayname,startdate,starttime) VALUES (?,?,?,?)";

			ps = dbconn.prepareStatement(query);
			ps.setInt(index++, Integer.valueOf(frontDisplayGroup.getId()));
			ps.setString(index++, frontDisplayGroup.getDisplayName());
			ps.setString(index++, frontDisplayGroup.getStartDate());
			ps.setString(index++, frontDisplayGroup.getStartTime());
			result = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			if(ps != null) ps.close();
		}
		return result;
	}

	/**
	 * フロントディレクトリ表示設定を更新する
	 * @param frontDisplayGroup フロントディレクトリ表示設定
	 * @return 更新件数
	 * @throws DBException 例外
	 */
	public int update(FrontDisplayGroup frontDisplayGroup) throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		int index = 1;
		try{
			String query = "UPDATE front_display_group SET displayname = ?,startdate = ?,starttime = ? WHERE id = ?";

			ps = dbconn.prepareStatement(query);
			ps.setString(index++, frontDisplayGroup.getDisplayName());
			ps.setString(index++, frontDisplayGroup.getStartDate());
			ps.setString(index++, frontDisplayGroup.getStartTime());
			ps.setInt(index++, Integer.valueOf(frontDisplayGroup.getId()));
			result = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}


	/**
	 * フロントディレクトリ表示設定を削除する
	 * @param frontDisplayGroup フロントディレクトリ表示設定
	 * @return 削除件数
	 * @throws DBException 例外
	 */
	public int delete(FrontDisplayGroup frontDisplayGroup) throws DBException {

		CustomPreparedStatement ps = null;
		int result = 0;
		int index = 1;
		try{
			String query = "DELETE FROM front_display_group WHERE id = ?";

			ps = dbconn.prepareStatement(query);
			ps.setInt(index++, Integer.valueOf(frontDisplayGroup.getId()));
			result = ps.executeUpdate();
		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps);
		}
		return result;
	}

	/**
	 * 名前をチェックする
	 * @param name 名前
	 * @param id ID
	 * @return true:重複する名前が存在、false:存在しない
	 * @throws DBException 例外
	 */
	public boolean checkName(String name, String id)throws DBException {

		CustomPreparedStatement pstmt = null;
		CustomResultSet rs = null;
		String sql = "";
		boolean state = false;
		try{
			if(StringUtils.isNotEmpty(id)){
				sql = "select displayname from front_display_group where displayname = ? and id != ?";
				pstmt = dbconn.prepareStatement(sql);
				pstmt.setString(1, name);
				pstmt.setString(2, id);
			}else{
				sql = "select displayname from front_display_group where displayname=?";
				pstmt = dbconn.prepareStatement(sql);
				pstmt.setString(1, name);
			}

			rs = pstmt.executeQuery();

			if (rs.next()) {
				state = true;
			} else {
				state = false;
			}
		}catch(DBException e) {
			throw e;
		} finally{
			CommonDBConnection.closeResource(pstmt, rs);

		}
		return state;
	}

	private String getDataName(String id) throws DBException {

		String name = null;
		StringBuilder sqlQuery = new StringBuilder();
		sqlQuery.append("select l6id,name from total_data inner join data on total_data.l6id = data.id where total_data.l6id = ?");

		CustomPreparedStatement ps = null;
		CustomResultSet rs = null;
		try{
			ps = dbconn.prepareStatement(sqlQuery.toString());
			ps.setString(1, id);
			rs = ps.executeQuery();

			while (rs.next()) {

				name = rs.getString("NAME");
			}

		}catch(DBException dbe){
			throw dbe;
		}finally{
			CommonDBConnection.closeResource(ps, rs);
		}

		return name;

	}

	private String makeGroupResultQuery(List<String> fieldList) {

		int size = fieldList.size();

		StringBuilder sqlQuery = new StringBuilder();

		sqlQuery.append("SELECT ");

		for (int i = 0; i < size; i++) {

			sqlQuery.append(String.format(SELECT_ITEM, i+1));
			sqlQuery.append(",");
		}
		sqlQuery.append("L1.TOTAL_DATA_ID ");

		sqlQuery.append("FROM ");

		boolean first = true;

		for (int j = 0; j < size; j++) {

			String fieldKey = fieldList.get(j);

			if (first) {

				first = false;

				sqlQuery.append(String.format(SELECT_FIELDKEY, fieldKey, j + 1));

			} else {

				sqlQuery.append("LEFT OUTER JOIN ");

				sqlQuery.append(String.format(SELECT_FIELDKEY, fieldKey, j + 1));

				sqlQuery.append(String.format(SELECT_JOIN_CONDITION, 1, j + 1));
			}
		}
		sqlQuery.append(" order by ");
		for (int k = 0; k < size; k++) {
			sqlQuery.append(String.format(ORDERBY_ITEM, k + 1));

			if (k != size - 1) {
				sqlQuery.append(",");
			}

		}

		return sqlQuery.toString();
	}

}
